package My

import (
	"fmt"
	"db2s/ETL"
	"db2s/global"
	"strings"
)

func (my TableInfoMeta) ForeignExist(s global.TablesMetaInfoInput) (result global.ObjectExistReturnResult, err error) {
	var f1 any
	var event = "[MySQLForeignExist]"
	result.BaseResult.Sql = fmt.Sprintf("select count(1) as `sum` from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_SCHEMA = '%v' and CONSTRAINT_NAME='%v'", s.TableInfo.Schema, s.TableInfo.Table)
	if result.BaseResult.TimeOut, f1, err = ExecSelectSql(ExecSqlInputS{
		Ss:           s,
		Event:        event,
		SelectSqlStr: result.BaseResult.Sql,
		Expect:       ETL.Map,
	}); err != nil || f1 == nil {
		return
	}
	if fmt.Sprintf("%v", f1.(map[string]any)["sum"]) == "1" {
		result.Result = true
	}
	return
}

func (my TableInfoMeta) ForeignName(s global.TablesMetaInfoInput) (result global.ObjectNameReturnResult, err error) {
	var f1 any
	var event = "[MySQLForeignName]"
	result.BaseResult.Sql = fmt.Sprintf("select CONSTRAINT_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_SCHEMA = '%v' %v",
		s.TableInfo.Schema, func() string {
			switch {
			case s.TableInfo.Table == "*":
				return ""
			case strings.Contains(s.TableInfo.Table, "%"):
				return fmt.Sprintf(" and CONSTRAINT_NAME like '%%%v%%'", s.TableInfo.Table)
			default:
				return fmt.Sprintf(" and CONSTRAINT_NAME = '%v'", s.TableInfo.Table)
			}
		}())
	if result.BaseResult.TimeOut, f1, err = ExecSelectSql(ExecSqlInputS{
		Ss:           s,
		Event:        event,
		SelectSqlStr: result.BaseResult.Sql,
		Expect:       ETL.StringGarth,
	}); err != nil || f1 == nil {
		return
	}
	result.Result = f1.([]string)
	return
}
func foreignPrintInfo(x *global.ForeignResultDefinition) (res string) {
	/*
		ALTER TABLE child_table1
		ADD CONSTRAINT fk_parent1
		FOREIGN KEY (parent_id, type) REFERENCES parent_table1(id, type);
	*/
	return fmt.Sprintf("ALTER TABLE `%v`.`%v` \n"+
		"ADD CONSTRAINT `%v` \n"+
		"FOREIGN KEY (`%v`) "+
		"REFERENCES `%v`.`%v`(`%v`) \n"+
		";", x.Schema, x.TableName,
		x.ForeignName, strings.Join(x.ForeignKey, "`,`"),
		x.RSchema, x.RTableName, strings.Join(x.RColumn, "`,`"),
	)
}
func ConstraintTable(s global.SchemaObjectInput) (res []string, err error) {
	var f1 any
	var event = "[MySQLConstraintTable]"
	sqlStr := fmt.Sprintf("SELECT \n"+
		"\t\tREFERENCED_COLUMN_NAME AS `column`\n"+
		"FROM \n    "+
		"INFORMATION_SCHEMA.KEY_COLUMN_USAGE\n"+
		"WHERE 1=1 \n    "+
		"AND TABLE_SCHEMA = '%v' \n"+
		"AND CONSTRAINT_NAME = '%v' \n"+
		"order by "+
		"ORDINAL_POSITION;\n", s.Schema, s.Table)
	if _, f1, err = ExecSelectSql(ExecSqlInputS{
		Ss:           s,
		Event:        event,
		SelectSqlStr: sqlStr,
		Expect:       ETL.SMap,
	}); err != nil || f1 == nil {
		return
	}
	for _, v := range f1.([]map[string]any) {
		res = append(res, fmt.Sprintf("%v", v["column"]))
	}
	return
}
func constraintsColumn(s global.SchemaObjectInput) (res []string, err error) {
	var (
		f1    any
		event = "[MySQLConstraintsColumn]"
	)
	sqlStr := fmt.Sprintf("SELECT \n"+
		"\t\tCOLUMN_NAME AS `column`\n"+
		"FROM \n    "+
		"INFORMATION_SCHEMA.KEY_COLUMN_USAGE\n"+
		"WHERE 1=1 \n    "+
		"AND TABLE_SCHEMA = '%v' \n"+
		"AND CONSTRAINT_NAME = '%v' \n"+
		"order by "+
		"ORDINAL_POSITION;\n", s.Schema, s.Table)
	if _, f1, err = ExecSelectSql(ExecSqlInputS{
		Ss:           s,
		Event:        event,
		SelectSqlStr: sqlStr,
		Expect:       ETL.SMap,
	}); err != nil || f1 == nil {
		return
	}
	for _, v := range f1.([]map[string]any) {
		res = append(res, fmt.Sprintf("%v", v["column"]))
	}
	return
}
func foreignDefinitionConvert(x global.ForeignResultDefinition) (convert global.ForeignDefinitionConvertS) {
	convert = global.ForeignDefinitionConvertS{
		Schema:      x.Schema,
		RSchema:     x.RSchema,
		TableName:   x.TableName,
		RTableName:  x.RTableName,
		ForeignName: x.ForeignName,
		RName:       x.RName,
		ForeignKey:  x.ForeignKey,
		RColumn:     x.RColumn,
		Type:        x.Type,
		PrintInfo:   x.PrintInfo,
	}
	return
}
func (my SchemaObjectMeta) Foreign(s global.SchemaObjectInput) (result global.SchemaObjectReturnResult, err error) {
	var (
		f1    any
		x     global.ForeignResultDefinition
		event = "[MySQLForeign]"
	)
	result.BaseResult.Sql = fmt.Sprintf("SELECT \n    "+
		"\tTABLE_SCHEMA AS `schema`,\n"+
		"\t\tREFERENCED_TABLE_SCHEMA AS `rSchema`,\n"+
		"\t\tTABLE_NAME AS `table`,\n"+
		"\t\tREFERENCED_TABLE_NAME AS `refTable`,\n"+
		"\t\tCONSTRAINT_NAME AS `name`,\n"+
		"\t\tCONSTRAINT_NAME AS `rName`\n"+
		"FROM \n    "+
		"INFORMATION_SCHEMA.KEY_COLUMN_USAGE\n"+
		"WHERE 1 = 1 \n    "+
		"AND TABLE_SCHEMA = '%v' \n"+
		"AND CONSTRAINT_NAME = '%v' \n"+
		"limit 1", s.Schema, s.Table)
	if result.BaseResult.TimeOut, f1, err = ExecSelectSql(ExecSqlInputS{
		Ss:           s,
		Event:        event,
		SelectSqlStr: result.BaseResult.Sql,
		Expect:       ETL.Map,
	}); err != nil || f1 == nil {
		return
	}
	if err = global.MapSubToStructSubJsonName(mapNullActive(f1.(map[string]any)), &x); err != nil {
		return
	}
	x.Type = "foreign"
	if x.RColumn, err = ConstraintTable(s); err != nil {
		return
	}
	if x.ForeignKey, err = constraintsColumn(s); err != nil {
		return
	}
	x.PrintInfo = foreignPrintInfo(&x)
	result.ForeignResult.Definition = x
	result.ForeignResult.Convert = foreignDefinitionConvert(x)
	return
}

func singleForeignCreateSql(y global.ForeignObjectResult, schema string) (res string) {
	/*
		ALTER TABLE child_table1
		ADD CONSTRAINT fk_parent1
		FOREIGN KEY (parent_id, type) REFERENCES parent_table1(id, type);
	*/
	x := y.Convert
	return fmt.Sprintf("ALTER TABLE `%v`.`%v` \n"+
		"ADD CONSTRAINT `%v` \n"+
		"FOREIGN KEY (`%v`) "+
		"REFERENCES `%v`.`%v`(`%v`) \n"+
		";", schema, x.TableName,
		x.ForeignName, strings.Join(x.ForeignKey, "`,`"),
		x.RSchema, x.RTableName, strings.Join(x.RColumn, "`,`"),
	)
}

func createForeign(s global.SchemaObjectInput) (result global.ObjectFixSqlS, err error) {
	var q = s.ObjectDefinition.ForeignResult
	result.CreateSql = append(result.CreateSql, fmt.Sprintf("use `%v`;", s.Schema))
	result.CreateSql = append(result.CreateSql, singleForeignCreateSql(q, s.Schema))
	return
}
